--    Program: 		CAPAREXT_CUSTOMER_PARTY_SITE.sql
--    Description:  Extract AR Customer Party Site information
--    Revision:     1st     
--    Parameter:    1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		   number;
   l_rejected_count 		   number;
   l_err_msg				   varchar2(2000);
                    		
   l_utl_file       		   utl_file.file_type;
   l_filename       		   varchar2(255);
   l_pathname       		   varchar2(100);
                    		
   l_ngo_sob				   varchar2(150);
   l_ngo_sob_id            	   number;
   l_business_group_id		   number;
                    		  
   cursor cur_party_site (p_business_group_id number) is   
      select * 
      from   hz_party_sites hps
      where  exists (select * 
                     from   chz_parties cp
                     where  cp.s_business_group_id = p_business_group_id
			         and    cp.party_id = hps.party_id);
begin
   l_pathname := '/tmp';
   l_filename := 'CAPAR_CUSTOMER_PARTY_SITE.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
  
   l_processed_count := 0;
   l_rejected_count  := 0;
      
   l_ngo_sob := '&1';
   
   begin
     select set_of_books_id
     into   l_ngo_sob_id
     from   gl_sets_of_books 
     where  upper(short_name) = upper(l_ngo_sob);
   exception
      when others then
        dbms_output.put_line(l_ngo_sob||': set of books name not found');
        --raise;
   end;
   
   begin
     select business_group_id
     into   l_business_group_id
     from   hr_organization_units 
     where  upper(name) = 'BG_'||upper(substr(l_ngo_sob, -4));
   exception
     when others then
       dbms_output.put_line(l_ngo_sob||': business group not found');
       --raise;
   end;      
   
   for cur in cur_party_site(l_business_group_id) loop   	
	  
  	  utl_file.put_line(l_utl_file, 
           cur.PARTY_SITE_ID              || chr(9) ||
           cur.PARTY_ID                   || chr(9) ||
           cur.LOCATION_ID                || chr(9) ||
           cur.LAST_UPDATE_DATE           || chr(9) ||
           cur.PARTY_SITE_NUMBER          || chr(9) ||
           cur.LAST_UPDATED_BY            || chr(9) ||
           cur.CREATION_DATE              || chr(9) ||
           cur.CREATED_BY                 || chr(9) ||
           cur.LAST_UPDATE_LOGIN          || chr(9) ||
           cur.REQUEST_ID                 || chr(9) ||
           cur.PROGRAM_APPLICATION_ID     || chr(9) ||
           cur.PROGRAM_ID                 || chr(9) ||
           cur.PROGRAM_UPDATE_DATE        || chr(9) ||
           cur.WH_UPDATE_DATE             || chr(9) ||
           cur.ATTRIBUTE_CATEGORY         || chr(9) ||
           cur.ATTRIBUTE1                 || chr(9) ||
           cur.ATTRIBUTE2                 || chr(9) ||
           cur.ATTRIBUTE3                 || chr(9) ||
           cur.ATTRIBUTE4                 || chr(9) ||
           cur.ATTRIBUTE5                 || chr(9) ||
           cur.ATTRIBUTE6                 || chr(9) ||
           cur.ATTRIBUTE7                 || chr(9) ||
           cur.ATTRIBUTE8                 || chr(9) ||
           cur.ATTRIBUTE9                 || chr(9) ||
           cur.ATTRIBUTE10                || chr(9) ||
           cur.ATTRIBUTE11                || chr(9) ||
           cur.ATTRIBUTE12                || chr(9) ||
           cur.ATTRIBUTE13                || chr(9) ||
           cur.ATTRIBUTE14                || chr(9) ||
           cur.ATTRIBUTE15                || chr(9) ||
           cur.ATTRIBUTE16                || chr(9) ||
           cur.ATTRIBUTE17                || chr(9) ||
           cur.ATTRIBUTE18                || chr(9) ||
           cur.ATTRIBUTE19                || chr(9) ||
           cur.ATTRIBUTE20                || chr(9) ||
           cur.GLOBAL_ATTRIBUTE_CATEGORY  || chr(9) ||
           cur.GLOBAL_ATTRIBUTE1          || chr(9) ||
           cur.GLOBAL_ATTRIBUTE2          || chr(9) ||
           cur.GLOBAL_ATTRIBUTE3          || chr(9) ||
           cur.GLOBAL_ATTRIBUTE4          || chr(9) ||
           cur.GLOBAL_ATTRIBUTE5          || chr(9) ||
           cur.GLOBAL_ATTRIBUTE6          || chr(9) ||
           cur.GLOBAL_ATTRIBUTE7          || chr(9) ||
           cur.GLOBAL_ATTRIBUTE8          || chr(9) ||
           cur.GLOBAL_ATTRIBUTE9          || chr(9) ||
           cur.GLOBAL_ATTRIBUTE10         || chr(9) ||
           cur.GLOBAL_ATTRIBUTE11         || chr(9) ||
           cur.GLOBAL_ATTRIBUTE12         || chr(9) ||
           cur.GLOBAL_ATTRIBUTE13         || chr(9) ||
           cur.GLOBAL_ATTRIBUTE14         || chr(9) ||
           cur.GLOBAL_ATTRIBUTE15         || chr(9) ||
           cur.GLOBAL_ATTRIBUTE16         || chr(9) ||
           cur.GLOBAL_ATTRIBUTE17         || chr(9) ||
           cur.GLOBAL_ATTRIBUTE18         || chr(9) ||
           cur.GLOBAL_ATTRIBUTE19         || chr(9) ||
           cur.GLOBAL_ATTRIBUTE20         || chr(9) ||
           cur.ORIG_SYSTEM_REFERENCE      || chr(9) ||
           cur.START_DATE_ACTIVE          || chr(9) ||
           cur.END_DATE_ACTIVE            || chr(9) ||
           cur.REGION                     || chr(9) ||
           cur.MAILSTOP                   || chr(9) ||
           cur.CUSTOMER_KEY_OSM           || chr(9) ||
           cur.PHONE_KEY_OSM              || chr(9) ||
           cur.CONTACT_KEY_OSM            || chr(9) ||
           cur.IDENTIFYING_ADDRESS_FLAG   || chr(9) ||
           cur.LANGUAGE                   || chr(9) ||
           cur.STATUS                     || chr(9) ||
           cur.PARTY_SITE_NAME            || chr(9) ||
           cur.ADDRESSEE                  || chr(9) ||
           cur.OBJECT_VERSION_NUMBER      || chr(9) ||
           cur.CREATED_BY_MODULE          || chr(9) ||
           cur.APPLICATION_ID             || chr(9) ||
           cur.ACTUAL_CONTENT_SOURCE
	   );
      
	  l_processed_count := l_processed_count + 1;
   end loop;
   
   l_err_msg := '';
   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := l_ngo_sob || ': Customer Party Site not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPAR_CUSTOMER_PARTY_SITE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;

   utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
end;
/     


--exit;
